package cn.com.edu.nju.ecrec.dataProcess;

import java.sql.*;

import javax.persistence.EntityManager;

import cn.com.edu.nju.ecrec.model.Item;
import cn.com.edu.nju.ecrec.util.JPAFactory;

public class ItemExtration {

	static void processItem() throws IllegalAccessException,
			InstantiationException, ClassNotFoundException, SQLException {
		EntityManager em = JPAFactory.getManager();
		String driverName = "com.mysql.jdbc.Driver";
		String userName = "root";
		String userPasswd = "123456";
		String url = "jdbc:mysql://localhost:3307/ECRec";
		Class.forName(driverName).newInstance();
		Connection con = DriverManager.getConnection(url, userName, userPasswd);
		Statement statement = con.createStatement();

		String sql = "select id,category_id,classify_id,brand_id,uri_name,goods_price from products";
		ResultSet rs = statement.executeQuery(sql);
		while (rs.next()) {
			Item item = new Item();

			long pid = rs.getLong(1);
			long category_id = rs.getLong(2);
			long classify_id = rs.getLong(3);
			long brand_id = rs.getLong(4);
			item.setUri_name(rs.getString(5));
			item.setGoods_price(rs.getDouble(6));

			Statement statement1 = con.createStatement();
			ResultSet rsCat = statement1
					.executeQuery("select title from categories where id="
							+ category_id);
			if (rsCat.first())
				item.setCategory(rsCat.getString(1));
			rsCat.close();

			ResultSet rsCal = statement1
					.executeQuery("select name from classifies where id="
							+ classify_id);
			if (rsCal.first())
				item.setClassify(rsCal.getString(1));
			rsCal.close();

			ResultSet rsBran = statement1
					.executeQuery("select name from brands where id="
							+ brand_id);
			if (rsBran.first())
				item.setBrand(rsBran.getString(1));
			rsBran.close();

			ResultSet rsSize = statement1
					.executeQuery("select name from attributeoptions where id in("
							+ "select attributeoption_id from product_attributeoption_relations where attribute_id=86"
							+ " and product_id=" + pid +")");
			if (rsSize.first())
				item.setColor(rsSize.getString(1));
			rsSize.close();
			
			statement1.close();
			
			System.out.println("item brand:"+item.getBrand()+" category:"+item.getCategory()+" classify:"+item.getClassify()+
					" uri_name:"+item.getUri_name()+" goods_price:"+item.getGoods_price()+" color:"+item.getColor());
			
			em.getTransaction().begin();
			em.persist(item);
			em.getTransaction().commit();
		}
		em.close();
		rs.close();
		statement.close();
		con.close();
	}

	public static void main(String[] args) throws SQLException,
			IllegalAccessException, InstantiationException,
			ClassNotFoundException {
		processItem();
	}
}
